﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

namespace AcessaDadosBD.Repositorios
{
    public class PrestadorRepository : IRepository<Prestador>
    {
        private IDbCommand Command { get; set; }

        public PrestadorRepository(IDbCommand cmd)
        {
            this.Command = cmd;
        }

        public Prestador GetById(int id)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idPrestador, nome, tipoLogradouro, logradouro, numero, complemento, codigoIBGE, municipio, codigoUF, CEP FROM tbPrestador WHERE idPrestador=" + id;
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                if (reader.Read())
                {
                    Prestador prestador = new Prestador()
                    {
                        ID = int.Parse(reader["idPrestador"].ToString()),
                        Nome = reader["nome"].ToString(),
                        TipoLogradouro = reader["tipoLogradouro"].ToString(),
                        Logradouro = reader["logradouro"].ToString(),
                        Numero = reader["numero"].ToString(),
                        Complemento = reader["complemento"].ToString(),
                        CodigoIBGE = reader["codigoIBGE"].ToString(),
                        Municipio = reader["municipio"].ToString(),
                        CodigoUF = reader["codigoUF"].ToString(),
                        CEP = reader["CEP"].ToString()
                    };
                    return prestador;
                }
                else
                {
                    throw new Exception("Prestador não encontrado");
                }
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public IQueryable<Prestador> GetAll()
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "SELECT idPrestador, nome, tipoLogradouro, logradouro, numero, complemento, codigoIBGE, municipio, codigoUF, CEP FROM tbPrestador";
                Command.CommandType = CommandType.Text;

                var reader = Command.ExecuteReader();

                List<Prestador> list = new List<Prestador>();
                while (reader.Read())
                {
                    Prestador prestador = new Prestador()
                    {
                        ID = int.Parse(reader["idPrestador"].ToString()),
                        Nome = reader["nome"].ToString(),
                        TipoLogradouro = reader["tipoLogradouro"].ToString(),
                        Logradouro = reader["logradouro"].ToString(),
                        Numero = reader["numero"].ToString(),
                        Complemento = reader["complemento"].ToString(),
                        CodigoIBGE = reader["codigoIBGE"].ToString(),
                        Municipio = reader["municipio"].ToString(),
                        CodigoUF = reader["codigoUF"].ToString(),
                        CEP = reader["CEP"].ToString()
                    };
                    list.Add(prestador);
                }
                return list.AsQueryable<Prestador>();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public int Insert(Prestador entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "INSERT INTO tbPrestador (nome, tipoLogradouro, logradouro, numero, complemento, codigoIBGE, municipio, codigoUF, CEP) VALUES ('" +
                    entity.Nome + "','" + entity.TipoLogradouro + "', '" + entity.Logradouro + "', '" + entity.Numero + "', '" + entity.Complemento + "', '" + entity.CodigoIBGE +
                    "', '" + entity.Municipio + "', '" + entity.CodigoUF + "', '" + entity.CEP + "')";
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();

                Command.CommandText = "SELECT @@IDENTITY";

                return int.Parse(Command.ExecuteScalar().ToString());
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Delete(Prestador entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "DELETE FROM tbPrestador WHERE idPrestador=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }

        public void Update(Prestador entity)
        {
            try
            {
                Command.Connection.Open();

                Command.CommandText = "UPDATE tbPrestador SET nome='" + entity.Nome + "', tipoLogradouro='" + entity.TipoLogradouro + "', logradouro='" + entity.Logradouro +
                    "', numero='" + entity.Numero + "', complemento='" + entity.Complemento + "', codigoIBGE='" + entity.CodigoIBGE + "', municipio='" + entity.Municipio +
                    "', codigoUF='" + entity.CodigoUF + "', CEP='" + entity.CEP + "' WHERE idPrestador=" + entity.ID;
                Command.CommandType = CommandType.Text;

                Command.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception("Erro ao executar operação: " + e.Message);
            }
            finally
            {
                Command.Connection.Close();
            }
        }
    }
}
